// nodejs 配置数据库
const mysql = require('mysql')
const db = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: '123456',
  database: 'student_admin'
})
module.exports = db

// 封装查询数据库的方法
function sqlQuery(sql, values, db) {
  return new Promise((resolve, reject) => {
    db.query(sql, values, (err, results, fields) => {
      if (err) reject(err)
      resolve(results)
    })
  })
}
module.exports = sqlQuery

// 数据库的模糊查询和范围查询  中间件
exports.sqlSearch = () => {
  return (req, res,next) => {
    // 增加数据库全局方法 按条件查询
    res.getSearchSql = (searchObj) => {
      let sql = ''
      for (let key in searchObj) {
        if (key.indexOf('Time') === -1 && key !== 'class') {
          sql += ` AND ${key} LIKE '%${searchObj[key]}%'`
        }
        // 班级不能模糊查询
        if (key === 'class' && searchObj[key] !== '') {
          sql += ` AND ${key} = '${searchObj[key]}'`
        }
      }
      return sql
    }
    // 增加数据库全局方法 按时间范围查询
    res.getRangeSql = (searchObj, keys = ['create_time', 'startTime', 'endTime']) => {
      let sql = ''
      sql += ` AND ${keys[0]} BETWEEN '${searchObj[keys[1]]}' AND '${searchObj[keys[2]]}'`
      return sql
    }
    next()
  }
}


// 由于设计三个表course test student, 所以这里使用多表查询
const sql = 'SELECT * FROM course c, test t, student s WHERE c.test_id = t.id AND c.student_id = s.id AND c.test_id = ? AND s.class = ?'

